/**业务管理流程平台版本1.0,生成时间:2017-04-13 13:16:48**/
package com.xbdun.dao.bmp.provider;

import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import com.xbdun.dao.bmp.entity.XbdunSysMenuDo;

/**
 * 动态XbdunSysMenuDo的SQL语句提供者
 * 菜单表
 * @author 张国涛
 * @2017-04-13 13:16:48
 */
public class SysMenuSqlProvider {

	//日志
	Log log = LogFactory.getLog(SysMenuSqlProvider.class);
	
	/**
	 * 动态sql更新对象
	 * @param map
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String updateDynXbdunSysMenuSql(Map<String,Object> map){
		
		StringBuilder sbSql = new StringBuilder();
		Map<String,Object> params = (Map<String, Object>) map.get("param");
		String _where = constWhere(map);
		boolean bl = false;
		
		Assert.isTrue(StringUtils.isNotEmpty(_where),"动态更新操作，条件Where不存在");
		
		//移除Where条件
		sbSql.append("UPDATE xbdun_sys_menu SET ");
		if(!params.isEmpty()){
			Iterator<Entry<String, Object>> iparam = params.entrySet().iterator();
			while(iparam.hasNext()){
				if(bl)sbSql.append(",");
				
				Entry<String, Object> enty = iparam.next();
				sbSql.append(enty.getKey()+" = "+"#{param."+enty.getKey()+"}");
				
				if(!bl)bl = true;
			}
			//设置Where条件
			if(!StringUtils.isEmpty(_where)){
				sbSql.append(" WHERE "+_where);
			}
		}else{
			//参数为空则，清空所有的SQL
			sbSql.setLength(0);
		}
		String sqlContent = sbSql.toString();
		
		sbSql.setLength(0);
		sbSql = null;
		
		return sqlContent;
	}

	/**
	 * 判断是否存在,不为null则是存在
	 * @param map
	 */
	public String existSql(Map<String,Object> params){
		StringBuilder sbSql = new StringBuilder();
		@SuppressWarnings("unchecked")
		Map<String,Object> mparam = (Map<String,Object>)params.get("param");
		boolean bl = false;
		sbSql.append("SELECT COUNT(*) FROM xbdun_sys_menu WHERE ");
		if(!params.isEmpty()){
			Iterator<Entry<String, Object>> iparam = mparam.entrySet().iterator();
			while(iparam.hasNext()){
				
				Entry<String, Object> enty = iparam.next();
				if(bl){sbSql.append(" AND ");}
				
				sbSql.append(enty.getKey()+" = "+"#{param."+enty.getKey()+"}");
				
				if(!bl)bl = true;
			}
		}
		
		String content = sbSql.toString();
		sbSql.setLength(0);
		sbSql = null;
		
		return content;
	}
	/**
	 * 查询指定父级菜单的子菜单最大菜单code后一位
	 * @param map
	 */
	public String getAddMenuCodeSql(Map<String,Object> map){
		StringBuilder sbSql = new StringBuilder();
		
		long code = (Long) map.get("code");
		// 查询子菜单的菜单编号最大值后一位
		if(code == 0){// 创建菜单为根菜单
			sbSql.append("SELECT IFNULL(MAX(code),99) + 1 FROM xbdun_sys_menu b WHERE b.code LIKE '___'");
		}else{
			sbSql.append("SELECT CONCAT(#{code},c.maxnum) FROM (SELECT (IFNULL(MAX(code),99)+1) maxnum FROM xbdun_sys_menu b WHERE b.code LIKE CONCAT(#{code},'___')) c");
		}
		
		String content = sbSql.toString();
		sbSql.setLength(0);
		sbSql = null;
		
		return content;
	}
	/**
	 * 查询分页
	 * @param params
	 * @return
	 */
	public String queryPagerSql(Map<String,Object> params){
		StringBuilder sbSql = new StringBuilder();
		//排序规则
		String orderBy = (String) params.get("_orderby");
		//当前页
		int currentIndex = (Integer) params.get("pageNum");
		//显示条数
		int numPerPage = (Integer) params.get("numPerPage");

		sbSql.append("SELECT * ");
		sbSql.append(" From xbdun_sys_menu Where 1=1 ");
		String where = constWhere(params);
		if(!StringUtils.isEmpty(where)){
			sbSql.append(" AND ");
			sbSql.append(where);
		}
		
		//查看排序
		if(!StringUtils.isEmpty(orderBy)){
			sbSql.append(" ORDER BY "+orderBy);
		}
		
		//分页
		if(numPerPage>0){
			sbSql.append(" LIMIT "+currentIndex+","+numPerPage);
		}
		
		String contentSql = sbSql.toString();
		
		sbSql.setLength(0);
		sbSql = null;
		
		return contentSql;
	}
	
	/**
	 * 查询分页查询总条数
	 * @param params
	 * @return
	 */
	public String queryPagerCountSql(Map<String,Object> params){
		StringBuilder sbSql = new StringBuilder();
		
		sbSql.append("SELECT COUNT(*) ");
		sbSql.append(" From xbdun_sys_menu Where 1=1 ");
		String where = constWhere(params);
		if(!StringUtils.isEmpty(where)){
			sbSql.append(" AND ");
			sbSql.append(where);
		}
		String contentSql = sbSql.toString();
		
		sbSql.setLength(0);
		sbSql = null;
		
		return contentSql;
		
	}
	
	public String addXbdunSysMenuSql(Map<String,Object> params){
		XbdunSysMenuDo xbdunMenuDo = (XbdunSysMenuDo)params.get("a");
		Integer platCode = (Integer)params.get("platCode");//获取到平台Code
		
		StringBuilder sbSql = new StringBuilder();
		sbSql.append("INSERT INTO xbdun_sys_menu(blPlat,blDisplay,code,name,hit,url,icoUrl,target,sort,enable,blDel,createTime,menuType)");
		sbSql.append("VALUES(#{a.blPlat},#{a.blDisplay},(");
		//设置菜单最大值
		if(xbdunMenuDo.getCode()<=0){
			//如果平台Code存在那么就保存平台Code
			if(null!=platCode&&platCode.intValue()>0){
				sbSql.append("#{platCode}");	//平台Code
			}else{
				sbSql.append("SELECT IFNULL(MAX(code),99)+1 FROM xbdun_sys_menu b WHERE b.code LIKE '___'");
			}
		}else{
			sbSql.append("select CASE LENGTH(c.maxnum) WHEN LENGTH(#{a.code}) THEN CONCAT(#{a.code},c.maxnum) WHEN 3 THEN CONCAT(#{a.code},c.maxnum) ELSE c.maxnum end  from (SELECT (IFNULL(MAX(code),99)+1) maxnum FROM xbdun_sys_menu b WHERE b.code LIKE CONCAT(#{a.code},'___')) c");
		}
		sbSql.append("),#{a.name},#{a.hit},#{a.url},#{a.icoUrl},#{a.target},#{a.sort},#{a.enable},#{a.blDel},now(),");
		if(xbdunMenuDo.getCode()>0){
			sbSql.append("(SELECT IF(length(trim(d.url))>0,3,#{a.menuType}) FROM xbdun_sys_menu d WHERE d.code= #{a.code})");
		}else{
			sbSql.append("#{a.menuType}");
		}
		sbSql.append(")");
		
		String sqlContent = sbSql.toString();
		sbSql.setLength(0);
		sbSql = null;
		
		return sqlContent;
	}
	
	//查询菜单，如果menuIds不为空则过滤掉
	public String queryOrgMenuSql(Map<String,Object> params){
		Long[] menuIds = (Long[])params.get("menuIds");	 //需要忽略的菜单
		long agentOrgCode = (Long)params.get("agentCode");//获取代理机构编号
		long orgCode = (Long)params.get("orgCode");//当前机构
		
		StringBuilder sbSql = new StringBuilder();
		sbSql.append("SELECT e.*,d.name,d.hit title FROM xbdun_sys_menu d,(");
		sbSql.append("SELECT a.code id,LEFT(a.code,LENGTH(a.code)-3) pId,");
		//判断是否为代理机构
		if(agentOrgCode!=orgCode){
			sbSql.append("IF(IFNULL(c.code,0)=0,0,1)");
		}else{
			sbSql.append("2");
		}
		sbSql.append(" checked FROM xbdun_sys_org_menus a");
		if(agentOrgCode!=orgCode){
			sbSql.append(" LEFT JOIN (");
			sbSql.append("SELECT b.code FROM xbdun_sys_org_menus b WHERE b.orgCode = #{orgCode}");
			sbSql.append(")c ON a.code = c.code");
		}
		//查询到代理机构编号
		sbSql.append("	WHERE a.orgCode = #{agentCode}");
		//排除菜单项
		if(null!=menuIds&&menuIds.length>0){
			sbSql.append("	AND a.code NOT IN(");
			for(int i=0;i<menuIds.length;i++){
				if(i>0){
					sbSql.append(",");
				}
				sbSql.append(menuIds[i]);
			}
			sbSql.append(")");
		}
		sbSql.append(")e WHERE d.blDel = 2 AND d.code = e.id ORDER BY d.sort ASC");
		String contentSql = sbSql.toString();
		
		sbSql.setLength(0);
		sbSql = null;
		
		return contentSql;
	}
	
	//代理机构菜单
	public String queryAgentOrgMenuSql(Map<String,Object> params){
		Long[] menuIds = (Long[])params.get("menuIds");
		StringBuilder sbSql = new StringBuilder();
		sbSql.append("SELECT m.code id,LEFT(m.code,LENGTH(m.code)-3) pId,m.name,m.hit title,IF(IFNULL(om.code,0)=0,0,1) checked FROM xbdun_sys_menu AS m");
		sbSql.append(" LEFT JOIN (SELECT b.code FROM xbdun_sys_org_menus b WHERE b.orgCode = #{orgCode}) om ON m.code = om.code");
		sbSql.append(" WHERE m.blDel = 2");
		if(null!=menuIds&&menuIds.length>0){
			sbSql.append(" AND m.code NOT IN(");
			for(int i=0;i<menuIds.length;i++){
				if(i>0){
					sbSql.append(",");
				}
				sbSql.append(menuIds[i]);
			}
			sbSql.append(")");
		}
		sbSql.append(" ORDER BY m.sort ASC");
		
		String contentSql = sbSql.toString();
		
		sbSql.setLength(0);
		sbSql = null;
		
		return contentSql;
	}
	//生成查询分页where条件
	private String constWhere(Map<String,Object> params){
		StringBuilder sbSql = new StringBuilder();
		//where查询条件
		@SuppressWarnings("unchecked")
		Map<String,Object> _where = (Map<String,Object>) params.get("_where");
		if(!CollectionUtils.isEmpty(_where)){
			Iterator<Entry<String, Object>> iparam = _where.entrySet().iterator();
			boolean bl = false;
			while(iparam.hasNext()){
				Entry<String, Object> enty = iparam.next();
				if(bl){sbSql.append(" and ");}
				
				sbSql.append(enty.getKey()+" = "+"#{_where."+enty.getKey()+"}");
				if(!bl)bl = true;
			}
		}
		
		String contentSql = sbSql.toString();
		
		sbSql.setLength(0);
		sbSql = null;
		
		return contentSql;
	}
	
}
